package jpb.util.load;

import jpb.module.daomain.department.Department;
import jpb.module.daomain.department.LoadDep;
import jpb.module.daomain.member.LoadMem;
import jpb.util.load.excel.ExcelRow;
import jpb.util.IndexUtil;
import jpb.util.load.excel.ExcelCellStyle;
import jpb.util.load.excel.SetValidation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * Created by haol on 2017/4/18.
 */
public class DownExcelUtil {


        private Logger logger = LoggerFactory.getLogger(LoadExcelUtil.class);

        //下载组织用的模板
        public HSSFWorkbook create(List<Department> departments,
                                   String name, String sheetName, String[] head, String[] pros) {


                int depsSize = departments.size();
                int strLengh = head.length;


                int width = depsSize * strLengh;
                HSSFWorkbook workbook = new HSSFWorkbook();

                HSSFSheet sheet = workbook.createSheet(sheetName);

                ExcelCellStyle cellStyle = new ExcelCellStyle(workbook);
                SetValidation validation = new SetValidation();

                HSSFPatriarch patr = sheet.createDrawingPatriarch();
                HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8, 3);//创建批注位置


                HSSFCell cell = null;
                CellRangeAddress range;
                HSSFRichTextString text;
                HSSFRow row;
                sheet.setDefaultColumnWidth(30);
                ExcelRow excelRow = new ExcelRow(workbook);

                //第0行

                excelRow.createRow(sheet, 0, name, width, 500);
//                row = sheet.createRow(0);
//                row.setHeight((short) 500);
//                cell = row.createCell(0);
//
//                cell.setCellValue(new HSSFRichTextString(name));
//                range = new CellRangeAddress(0, 0, 0, width - 1);
//                sheet.addMergedRegion(range);
//
//                cell.setCellStyle(cellStyle.headStyle());
//                validation.setDataValidation(sheet, cell);


                //第1行
                row = sheet.createRow(1);
                for (int i = 0; i < depsSize * strLengh; i = i + strLengh) {
                        HSSFComment comment = patr.createCellComment(anchor);
                        //创建批注
                        comment.setString(new HSSFRichTextString(departments.get(i / strLengh).getIntroduce()));
                        //设置批注内容
                        cell = row.createCell(i);
                        range = new CellRangeAddress(1, 1, i, i + strLengh - 1);
                        String depName = departments.get(i / strLengh).getName() + "(" + departments.get(i / strLengh).getId() + ")";
                        text = new HSSFRichTextString(depName);
                        cell.setCellValue(text);
                        cell.setCellStyle(cellStyle.alignStyle());
                        cell.setCellComment(comment);
                        sheet.addMergedRegion(range);
                        validation.setDataValidation(sheet, cell);

                }
                int colWidth;

                //第2行
                excelRow.createRowHead(sheet, head, 2,depsSize);
//                row = sheet.createRow(2);
//                for (int i = 0; i < depsSize * strLengh; i++) {
//                        cell = row.createCell(i);
//                        text = new HSSFRichTextString(head[i % strLengh]);
//                        cell.setCellValue(text);
//                        cell.setCellStyle(cellStyle.alignStyle());
//                        validation.setDataValidation(sheet, cell);
//                }


                //第3行到100
                int firstRow = 3;
                int lastRow = 100;
                int dex = IndexUtil.getINDEX("职称", head);
                if (dex != -1) {
                        for (int i = 0; i < depsSize * strLengh; i++) {
                                if (i % (head.length) == dex) {
                                        validation.setProsValidation(sheet, cell, firstRow, lastRow, i, i, pros);
                                }
                        }
                }
                int firstCol = 0;
                int lastCol = width;
                cellStyle.setLotCell(sheet, firstRow, lastRow, firstCol, lastCol, cellStyle.textStyle());

                return workbook;
        }

        //下载批量注册用的模板
        public HSSFWorkbook create(
                String name, String sheetName, String[] head) {

                int strLengh = head.length;

                HSSFWorkbook workbook = new HSSFWorkbook();
                ExcelRow excelRow = new ExcelRow(workbook);
                HSSFSheet sheet = workbook.createSheet(sheetName);
                sheet.setDefaultColumnWidth(20);
                ExcelCellStyle cellStyle = new ExcelCellStyle(workbook);
                SetValidation validation = new SetValidation();

                HSSFCell cell;
                CellRangeAddress range;
                HSSFRow row;


                //第0行
                excelRow.createRow(sheet, 0, name, strLengh, 500);
//                row = sheet.createRow(0);
//                row.setHeight((short) 500);
//                cell = row.createCell(0);
//
//                cell.setCellValue(new HSSFRichTextString(name));
//                range = new CellRangeAddress(0, 0, 0, strLengh - 1);
//                sheet.addMergedRegion(range);
//                cell.setCellStyle(cellStyle.headStyle());
//                sheet.setDefaultColumnWidth(30);
//                validation.setDataValidation(sheet, cell);


                //第1行
                excelRow.createRow(sheet, 1, "请注意保护隐私", strLengh, 300);
//                row = sheet.createRow(1);
//                row.setHeight((short) 300);
//                cell = row.createCell(0);
//                cell.setCellStyle(cellStyle.alignStyle());
//                cell.setCellValue(new HSSFRichTextString("请注意保护隐私"));
//                range = new CellRangeAddress(1, 1, 0, strLengh - 1);
//                sheet.addMergedRegion(range);
//                sheet.setDefaultColumnWidth(30);
//                validation.setDataValidation(sheet, cell);

                //第2行

                excelRow.createRowHead(sheet, head, 2);

                //设置格式为文本
                //3-99行
                int firstCol = 0;
                cellStyle.setLotCell(sheet, 3, 99, firstCol, strLengh, cellStyle.textStyle());

                return workbook;
        }

        //下载组织成员信息表
        public HSSFWorkbook createContainMsg(List<LoadDep> loadDeps, String name, String sheetName, String[] head) {

                int strLengh = head.length;

                HSSFWorkbook workbook = new HSSFWorkbook();
                ExcelRow excelRow = new ExcelRow(workbook);

                HSSFSheet sheet = workbook.createSheet(sheetName);
                sheet.setDefaultColumnWidth(30);
                ExcelCellStyle cellStyle = new ExcelCellStyle(workbook);
                SetValidation validation = new SetValidation();
                sheet.setDefaultColumnWidth(20);
                HSSFCell cell;
                CellRangeAddress range;
                HSSFRow row;


                //第0行
//                row = sheet.createRow(0);
//                row.setHeight((short) 500);
//                cell = row.createCell(0);
//
//                cell.setCellValue(new HSSFRichTextString(name));
//                range = new CellRangeAddress(0, 0, 0, strLengh - 1);
//                sheet.addMergedRegion(range);
//                cell.setCellStyle(cellStyle.headStyle());
//                sheet.setDefaultColumnWidth(30);
//                validation.setDataValidation(sheet, cell);
                excelRow.createRow(sheet, 0, name, strLengh, 500);


                //第1行
                excelRow.createRow(sheet, 1, "请注意保护隐私", strLengh, 300);
//                row = sheet.createRow(1);
//                row.setHeight((short) 300);
//                cell = row.createCell(0);
//                cell.setCellStyle(cellStyle.alignStyle());
//                cell.setCellValue(new HSSFRichTextString("请注意保护隐私"));
//                range = new CellRangeAddress(1, 1, 0, strLengh - 1);
//                sheet.addMergedRegion(range);
//                sheet.setDefaultColumnWidth(20);
//                validation.setDataValidation(sheet, cell);
                //第2行

                excelRow.createRowHead(sheet, head, 2);

                //第2行
                int roww = 3;
                for (LoadDep loadDep : loadDeps) {
                        for (LoadMem member : loadDep.getMembers()) {
                                if (member.getMemberNum().equals("201430098009")){
                                        System.out.println();
                                }
                                HSSFRow rows = sheet.createRow(roww);
                                rows.createCell(0).setCellValue(new HSSFRichTextString(member.getDepName()));
                                rows.createCell(1).setCellValue(new HSSFRichTextString(member.getMemberNum()));
                                rows.createCell(2).setCellValue(new HSSFRichTextString(member.getName()));
                                String login = "YES";
                                if (member.getLoginTime() == null) {
                                        login = "N0";
                                }
                                rows.createCell(3).setCellValue(new HSSFRichTextString(login));
                                rows.createCell(4).setCellValue(new HSSFRichTextString(member.getUpdateTime()));

                                rows.createCell(5).setCellValue(new HSSFRichTextString(member.getMobile()));
                                rows.createCell(6).setCellValue(new HSSFRichTextString(member.getProName()));
                                rows.createCell(7).setCellValue(new HSSFRichTextString(member.getIntroduce()));
                                rows.createCell(8).setCellValue(new HSSFRichTextString(member.getWeChat()));
                                rows.createCell(9).setCellValue(new HSSFRichTextString(member.getEmail()));
                                rows.createCell(10).setCellValue(new HSSFRichTextString(member.getSite()));
                                rows.createCell(11).setCellValue(new HSSFRichTextString(member.getNewBirthday()));
                                rows.createCell(12).setCellValue(new HSSFRichTextString(member.getOldBirthday()));
                                roww++;
                        }
                }
                return workbook;
        }

}
